# dao/student_statistic_dao.py

from sqlalchemy import desc
from app.dao.models.mysql_gen import StudentStatistic
from .base_dao import getDatabaseSession


class StudentStatisticDAO:
    @classmethod
    def delete_all(cls):
        with getDatabaseSession() as session:
            session.query(StudentStatistic).delete()
            session.commit()

    @classmethod
    def update_statistics(cls, statistics: list[dict]):
        with getDatabaseSession() as session:
            for stat in statistics:
                existing = (
                    session.query(StudentStatistic)
                    .filter_by(student_id=stat["student_id"])
                    .first()
                )
                if existing:
                    for key, value in stat.items():
                        setattr(existing, key, value)
                else:
                    session.add(StudentStatistic(**stat))
            session.commit()

    @classmethod
    def get_by_major(cls, major: str):
        with getDatabaseSession() as session:
            return (
                session.query(StudentStatistic)
                .filter(StudentStatistic.major.contains(major))
                .order_by(desc(StudentStatistic.avg_gpa))
                .all()
            )

    @classmethod
    def get_by_sid(cls, student_id: str) -> StudentStatistic:
        with getDatabaseSession() as session:
            return (
                session.query(StudentStatistic)
                .filter(StudentStatistic.student_id == student_id)
                .first()
            )
